﻿Public Class f_CreateTable
    Dim eApp As Excel.Application
    Sub FindHeaders()
        Dim r As Excel.Range
        Dim row As Integer = -1
        Dim innerRow As Integer
        Dim col As Integer = -1
        Dim newItem As Integer
        Dim rowParams As String() = {"", "", ""}
        Dim i As Integer
        Dim l As Integer
        Dim maxLen As Integer
        Dim s As String

        Try

            Me.dg_Columns.Rows.Clear()

            r = eApp.ActiveCell
            s = If(IsNothing(r.Offset(row, 0).Value), "", (r.Offset(row, 0).Value.ToString))
            While s <> ""
                row = row - 1
                s = If(IsNothing(r.Offset(row, 0).Value), "", (r.Offset(row, 0).Value.ToString))
            End While

            s = If(IsNothing(r.Offset(row + 1, col).Value), "", (r.Offset(row + 1, col).Value.ToString))
            While s <> ""
                col = col - 1
                s = If(IsNothing(r.Offset(row + 1, col).Value), "", (r.Offset(row + 1, col).Value.ToString))
            End While
            s = If(IsNothing(r.Offset(row + 1, col + 1).Value), "", r.Offset(row + 1, col + 1).Value.ToString)
            While s <> ""
                rowParams(0) = s

                If InStr(r.Offset(row + 2, col + 1).DisplayFormat.NumberFormat, "m\") > 0 Then

                    rowParams(1) = "date"
                ElseIf r.Offset(row + 2, col + 1).DisplayFormat.NumberFormat = "General" Then
                    rowParams(1) = "varchar"
                    innerRow = row + 2
                    maxLen = 0
                    l = 0
                    For i = 0 To (Me.tb_RowsToScan.Text - 1)
                        l = Len(If(IsNothing(r.Offset(innerRow + i, col + 1).Value), "", (r.Offset(innerRow + i, col + 1).Value.ToString)))
                        maxLen = If(maxLen > l, maxLen, l)
                    Next
                    rowParams(2) = maxLen
                End If
                newItem = Me.dg_Columns.Rows.Add(rowParams)
                col = col + 1
                s = If(IsNothing(r.Offset(row + 1, col + 1).Value), "", r.Offset(row + 1, col + 1).Value.ToString)
            End While

        Catch ex As Exception
            MsgBox(ex.Message & vbCr & ex.StackTrace)
        End Try

    End Sub
    Private Sub f_CreateTable_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        eApp = ThisAddIn.eApp

        Me.tb_TableName.Text = eApp.ActiveSheet.name
        Me.dg_Columns.AllowUserToAddRows = False
        Me.dg_Columns.AllowUserToDeleteRows = False
        Me.dg_Columns.AllowUserToOrderColumns = False

        FindHeaders()



    End Sub

  
    Private Sub b_Scan_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles b_Scan.Click
        FindHeaders()

    End Sub
    Function GetCreateSQL() As String
        Dim row As System.Windows.Forms.DataGridViewRow

        Dim com As New StringBuilder
        Try

            com.AppendFormat("CREATE TABLE `{0}` (", Me.tb_TableName.Text)


            For Each row In Me.dg_Columns.Rows
                Select Case row.Cells(1).Value
                    Case "varchar"
                        com.AppendFormat("`{0}` {1}( {2} ),",
                            row.Cells(0).Value, row.Cells(1).Value, If(row.Cells(2).Value = 0, 1, row.Cells(2).Value))
                    Case "date"
                        com.AppendFormat("`{0}` {1} ,",
                            row.Cells(0).Value, row.Cells(1).Value)
                    Case "int"
                        com.AppendFormat("`{0}` {1} ),",
                            row.Cells(0).Value, row.Cells(1).Value)

                End Select
            Next
            com.Replace(",", ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ; ", com.Length - 1, 1)

        Catch ex As Exception
            MsgBox(ex.Message & vbCr & ex.StackTrace)

        End Try

        GetCreateSQL = com.ToString
    End Function
    Private Sub b_Import_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles b_Import.Click
        Dim msqlConnection As MySql.Data.MySqlClient.MySqlConnection
        Dim msqlCommand As MySql.Data.MySqlClient.MySqlCommand = New MySql.Data.MySqlClient.MySqlCommand()
        Dim msqlReader As MySql.Data.MySqlClient.MySqlDataReader
        Dim conStr As New StringBuilder
        Dim createSQL As String
        Dim result As Integer
        Try
            My.Settings.Save()
            conStr.AppendFormat("server={0};user id={1};Password={2};database={3};persist security info=False",
                                Me.tb_server.Text, Me.tb_username.Text, Me.tb_password.Text, Me.tb_database.Text)


            msqlConnection = New MySql.Data.MySqlClient.MySqlConnection(conStr.ToString)


            
            msqlConnection.Open()
            msqlCommand.Connection = msqlConnection
            Try
                createSQL = "drop table `" & Me.tb_TableName.Text & "`;"
                msqlCommand.CommandText = createSQL
                result = msqlCommand.ExecuteNonQuery
            Catch ex As Exception
            End Try



            createSQL = GetCreateSQL()
            msqlCommand.CommandText = createSQL
            result = msqlCommand.ExecuteNonQuery

            ThisAddIn.eApp.ActiveWorkbook.SaveAs(, Excel.XlFileFormat.xlCSV)


            Dim bl As New MySql.Data.MySqlClient.MySqlBulkLoader(msqlConnection)
            Dim rowsloaded As Integer

            bl.TableName = "`" & Me.tb_TableName.Text & "`"
            bl.FieldTerminator = ","
            bl.LineTerminator = "\n"
            bl.FieldQuotationCharacter = """"

            bl.FileName = ThisAddIn.eApp.ActiveWorkbook.FullName
            bl.NumberOfLinesToSkip = 2
            ThisAddIn.eApp.ActiveWorkbook.Close()

            rowsloaded = bl.Load
            MsgBox(rowsloaded & " rows loaded to " & bl.TableName)

          
        Catch ex As Exception
            MsgBox(ex.Message & vbCr & ex.StackTrace)
        Finally
            msqlConnection.Close()
        End Try
    End Sub

    Private Sub b_Cancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles b_Cancel.Click
        Me.Close()

    End Sub
End Class